package Shop.tools;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by BadHuge on 2020/9/11.
 */
public class DBHelper {

        private static Connection connection = null;
        private static Statement statement = null;
        private static ResultSet resultSet = null;
        private static String url = "jdbc:mysql://localhost:3306/shopping";


        //创建连接
        private static Connection getConn(){
            try {
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection(url,"root","111111");
            }catch (Exception e){
                e.printStackTrace();
            }
            return connection;
        }

        //关闭
        private static void closeAll(){
            try{
                if(resultSet != null){
                    resultSet.close();
                }
                if(statement != null){
                    statement.close();
                }
                if(connection != null){
                    connection.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }

        //查询
        public static List<Map<String,Object>> query(String sql){
            try{
                connection = getConn();
                statement = connection.createStatement();
                resultSet = statement.executeQuery(sql);
                List<Map<String,Object>> data = new ArrayList<>();
                while (resultSet.next()){
                    //获取各个列
                    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                    int columnCount = resultSetMetaData.getColumnCount();
                    HashMap<String,Object> row = new HashMap<>();
                    for (int i = 1; i <= columnCount;i++) {
                        String columnName = resultSetMetaData.getColumnName(i);
                        row.put(columnName,resultSet.getObject(i));
                    }
                    data.add(row);
                }
                return data;
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                closeAll();
            }
            return null;
        }
        //非查询
        public static int executeUpdata(String sql){
            try {
                connection = getConn();
                statement = connection.createStatement();
                int flag = statement.executeUpdate(sql);
                return flag;
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                closeAll();
            }
            return 0;
        }

}
